#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb
import sys

def createCode(nom):
	n=nom.upper();
	r="";
	for i in range(0, len(n)):
		if n[i] >= "A" and n[i] <= "Z":
			r+=n[i]	
	if len(r) > 6:
		r=r[0:6]
	if len(r) < 6:
		for i in range(0, 6-len(r)):
			r+="0"
	return r;
		
def inListe(liste, code):
	try:
		liste.index(code)
		return True
	except:
		return False

def incCode(code):
	num=0
	for i in range(0, 6):
		if code[i] >= "0" and code[i] <= "9":
			num*=10;
			num+=int(code[i])
	num+=1
	strNum=str(num)
	code=code[0:6-len(strNum)]+strNum
	return code
		
def calculEffectif(effectif):
	if effectif < 6:
		return "1 à 5"
	if effectif < 10:
		return "6 à 9"
	if effectif < 20:
		return "10 à 19"
	if effectif < 50:
		return "20 à 49"
	if effectif < 100:
		return "50 à 99"
	if effectif < 200:
		return "100 à 199"
	return "plus de 200"
	
db=MySQLdb.connect("ns", "admin", "leane24", "amanda")
c=db.cursor()
c.execute("SET NAMES 'utf8'")
c.execute("SET CHARACTER SET utf8")
c.execute("select code from prospect order by code")
rows=c.fetchall()
liste=[]

for row in rows:
	liste+=[row[0]]

c.execute("select nom from assureur")
rows=c.fetchall()
#code="ASS000"
for row in rows:
	code=createCode(row[0]);

	while inListe(liste, code):
		code=incCode(code)
	
	liste+=[code]
	requete="insert into prospect "
	requete+="(code, nom, adresse1, adresse2, codePostal, ville, pays, telephone, fax, creation, createur, activite, flag, effectif) "
	requete+="select \"%s\", nom, adresse1, adresse2, cp, ville, \"FRANCE\", tel, fax, curdate(), user(), \"Assureur\", flag, \"1\" " % (code)
	requete+="FROM assureur WHERE id = %s LIMIT 1" % (row[0])
	
	try:
		c.execute(requete)
	
		requete="insert into contactProspect (prospect, numero, nom, fonction, mail, defaut) select \"%s\", 1, nomDuGars, \"Dirigeant\", mail, \"X\" " % (code)
		requete+="from assureur where id = %s and nomDuGars <> \"\" LIMIT 1" % (row[0])
	
		c.execute(requete)
	except:
		print row[0], code
		c.close()
		sys.exit
		
c.close()